﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.IO;
using System.Reflection;
using Agos.Utils;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.Windows.Forms;

namespace DeutscheRemoval.Utils
{
    class ExcelUtils
    {
        [DllImport("user32.dll")]
        static extern int GetWindowThreadProcessId(int hWnd, out int lpdwProcessId);
       
        /// <summary>
        /// Export dataTable to excel file
        /// </summary>
        public static string Export(string path, DataTable dt, string fileName)
        {
            string output = "OK";
            Excel.ApplicationClass excel = null;
            try
            {
                //create a new instance of excel file 
                excel = new Excel.ApplicationClass();
                excel.Application.Workbooks.Add(true);

                //first let's extract the columns and create a little header
                //save the excel file
                Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
                worksheet.Activate();
                worksheet.Name = "input-output";

                int horizontal = 1;
                int vertical = 1;
                //foreach (DataColumn dc in dt.Columns)
                //{
                //    excel.Cells[vertical, horizontal] = dc.ColumnName;
                //    horizontal++;
                //}
                ////go to next row
                //vertical++;

                foreach (DataRow row in dt.Rows)
                {
                    horizontal = 1;
                    foreach (DataColumn column in dt.Columns)
                    {
                            ((Excel.Range)excel.Cells[vertical, horizontal]).NumberFormat = "@";
                        
                        excel.Cells[vertical, horizontal] = row[column];
                        horizontal++;
                    }
                    vertical++;
                    if (vertical == 500) break;
                }
                int x = 0;
                DirectoryInfo dr = new DirectoryInfo(path);
                dr.Create();
                FileInfo fi = new FileInfo(path + fileName);
                if (fi.Exists == true)
                    fi.Delete();
                worksheet.SaveAs(path + fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                excel.Workbooks.Close();
                excel.Quit();

            }
            catch (Exception ex)
            {
                output = ex.Message;
                Debug.WriteLine("ce plm" + ex.Message);
                MessageBox.Show(ex.Message);
            }
            finally
            {
                TryToKillXlApp(excel);
            }
            return output;
        }
        public static string FastExport(string path, DataTable dt, string fileName)
        {
            string output = "OK";
            Excel.ApplicationClass excel = null;
            try
            {
                excel = new Excel.ApplicationClass();
                excel.Visible = false;
                excel.DisplayAlerts = false;
                excel.Application.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
                worksheet.Activate();
                worksheet.Name = "input-output";

                // Create Array to hold the data of DataTable
                object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];

                //Fill DataTable in Array
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    DataRow drr = dt.Rows[r];
                    for (int c = 0; c < dt.Columns.Count; c++)
                    {
                        arr[r, c] = drr[c];
                    }
                }
               Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
               Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1 + dt.Rows.Count - 1, dt.Columns.Count];
               Excel.Range range = worksheet  .get_Range(c1, c2); 

                //Fill Array in Excel
                range.NumberFormat = "@";
                range.Value2 = arr; 
                DirectoryInfo dr = new DirectoryInfo(path);
                if (!dr.Exists)
                {
                    dr.Create();
                }
                FileInfo fi = new FileInfo(path + fileName);
                if (fi.Exists == true)
                    fi.Delete();
                
                worksheet.SaveAs(path + fileName, Excel.XlFileFormat.xlExcel7
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                

                excel.Workbooks.Close();
                excel.Quit();
            }
            catch (Exception ex)
            {
                output = ex.Message;
                //Debug.WriteLine("ce plm" + ex.Message);
                MessageBox.Show(ex.Message);

            }
            finally
            {
                TryToKillXlApp(excel);
            }
            
            return output;
        }
       //return a 2d array with all the rows in the excel file
        public static object[,] Reader(string path, string filename)
        {
            string filePath = path;
            string fileName = filename;
            object[,] values = null;
            Excel.Application app = null ;
            Excel.Workbook workbook = null ;
            Excel.Worksheet sheet = null ;
            Excel.Range range = null; // much faster by using a range than reading the cells individually
            try
            {
                app = new Excel.Application();
                workbook = app.Workbooks.Open(filePath + fileName,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Excel.XlPlatform.xlWindows,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value);

                sheet = (Excel.Worksheet)workbook.Worksheets[1];
                range = sheet.get_Range("A1", Missing.Value);

                range = range.get_End(Excel.XlDirection.xlToRight);
                range = range.get_End(Excel.XlDirection.xlDown); //put your hands on the range A1 to bottom right corner
                string downAddress = range.get_Address(
                    false, false, Excel.XlReferenceStyle.xlA1,
                    Type.Missing, Type.Missing);
                range = sheet.get_Range("A1", downAddress); //entire range from A1 to downAddress
                
                values = (object[,])range.Value2;
            }

            catch (Exception exp)
            {
                Logger.Log(exp.Message.ToString(), LogType.File);
            }
            finally
            {
                TryToKillXlApp(app);
            }
            return values;
        }
        
        
        public static DataTable DTReader(string path, string fileName)
        {
            return ArrayToDT(Reader(path, fileName));
        }
       
        //convert the 2d array to DataTable
        // the 2d array starting index is 1 for both dimensions
        private static DataTable ArrayToDT(object [,] input)
        {
            DataTable _myDataTable = new DataTable();
            for (int i = 1; i <= input.GetLength(1); i++) //all the columns
            {
                _myDataTable.Columns.Add(input[1,i].ToString());
            }

            for (int j = 0; j < input.GetLength(0); j++) //linia
            {
                DataRow row = _myDataTable.NewRow();
                for (int i = 0; i < input.GetLength(1); i++) //coloana
                {
                    //use the static OleDoubleToDate method to convert double to DateTime
                    // by default interop excel read dates as doubles
                    // the date is on row  i ==   5..6..7..11
                    if ( j > 0 &&
                        ( i == 4 || i == 5 || i == 6 || i == 10  )
                        )
                    {
                        try
                        {
                            row[i] = DateTime.FromOADate((double)(input[j + 1, i + 1])).ToShortDateString();//covert double to DateTime
                        }
                        catch (Exception e)
                        {
                            Logger.Log(e.Message.ToString(), LogType.Operation);
                            row[i] = input[j + 1, i + 1]; //date can be missing (blank)
                        }
                    }
                    else
                        try
                        {
                            row[i] = input[j + 1, i + 1];
                        }
                        catch (Exception e)
                        {
                            Logger.Log(e.Message.ToString(), LogType.Operation);
                        }
                }
                _myDataTable.Rows.Add(row);
            }
            return _myDataTable;
        }

        static void TryToKillXlApp(Excel.Application xlApp)
        {
            if (xlApp != null)
            {
                int excelProcessId = 0;
                GetWindowThreadProcessId(xlApp.Hwnd, out excelProcessId);
                Process p = Process.GetProcessById(excelProcessId);
                p.Kill();
                xlApp = null;
            }
        }
    }
}
